﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Foothill.Common.Logging;
using Foothill.DataAccess.Interface;
using Foothill.Model;

namespace Foothill.DataAccess.Repository
{
    public class AdRepo : DbAccessBase, IAdRepo
    {
        // insert a new for sale ad to database
        public int InsertNewForSaleAd(ForSaleAd newAd)
        {
            DbCommand dbCommand = FoothillDB.GetStoredProcCommand("InsertNewForSaleAd");
            
            FoothillDB.AddInParameter(dbCommand, "ModelId", DbType.Int32, newAd.ModelId);
            FoothillDB.AddInParameter(dbCommand, "TransmissionTypeId", DbType.Int32, newAd.TransmissionTypeId);
            FoothillDB.AddInParameter(dbCommand, "ColorId", DbType.Int32, newAd.ColorId);
            FoothillDB.AddInParameter(dbCommand, "YearMade", DbType.Int32, newAd.YearMade);
            FoothillDB.AddInParameter(dbCommand, "Price", DbType.Int32, newAd.Price);
            FoothillDB.AddInParameter(dbCommand, "MileAge", DbType.Int32, newAd.MileAge);
            FoothillDB.AddInParameter(dbCommand, "Description", DbType.String, newAd.Description);
            FoothillDB.AddInParameter(dbCommand, "TimePosted", DbType.DateTime, newAd.LastEditTime);
            FoothillDB.AddInParameter(dbCommand, "ContactPhone", DbType.String, newAd.ContactPhone);
            FoothillDB.AddInParameter(dbCommand, "ContactEmail", DbType.String, newAd.ContactEmail);
            FoothillDB.AddInParameter(dbCommand, "ContactName", DbType.String, newAd.ContactName);
            FoothillDB.AddInParameter(dbCommand, "EditKey", DbType.String, newAd.EditKey);
            FoothillDB.AddInParameter(dbCommand, "StatusId", DbType.Int32, newAd.StatusId);
            FoothillDB.AddInParameter(dbCommand, "CityId", DbType.Int32, newAd.CityId);
            FoothillDB.AddInParameter(dbCommand, "LastEditTime", DbType.DateTime, newAd.LastEditTime);
            FoothillDB.AddInParameter(dbCommand, "ListingTypeId", DbType.Int32, newAd.ListingTypeId);
            FoothillDB.AddInParameter(dbCommand, "VehicleConditionId", DbType.Int32, newAd.VehicleConditionId);
            FoothillDB.AddInParameter(dbCommand, "PostedByUserAccountId", DbType.Int32, newAd.PostedByUserAccountId);

            FoothillDB.AddOutParameter(dbCommand, "@NewAdID", DbType.Int32, 4);

            int newAdId = 0;
            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
                newAdId = Convert.ToInt32(FoothillDB.GetParameterValue(dbCommand, "NewAdID"));
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException("Error when inserting a new ad to database", exception);
            }

            return newAdId;
        }

        public bool UpdateForSaleAdEditKey(int adId, string editkey)
        {
            string sql = "UPDATE AdForSale SET EditKey=@editkey WHERE AdId=@adId";
            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            
            FoothillDB.AddInParameter( dbCommand, "adId", DbType.Int32, adId );
            FoothillDB.AddInParameter( dbCommand, "editkey", DbType.String, editkey );
            int rowAffected = 0;
            try
            {
                rowAffected = FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when updating Edit Key for ad id={0}", adId), exception);
            }

            return (rowAffected > 0);
        }

        public bool SetPostStatusId(int postId, AdStatusLookup statusId)
        {
            string sql = "UPDATE [AdForSale] SET [StatusId] = @StatusId, [LastEditTime]=@lastEditTime WHERE AdId=@postId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "postId", DbType.Int32, postId);
            FoothillDB.AddInParameter(dbCommand, "StatusId", DbType.Int32, Convert.ToInt32(statusId));
            FoothillDB.AddInParameter(dbCommand, "lastEditTime", DbType.DateTime, DateTime.Now);

            int rowAffected = 0;
            try
            {
                rowAffected = FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when set status for ad id={0}", postId), exception);
            }

            return (rowAffected > 0);
        }

        public bool UpdatePrice(int postId, int newPrice)
        {
            string sql = "UPDATE [AdForSale] SET Price = @price, [LastEditTime]=@lastEditTime WHERE AdId=@postId";

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);

            FoothillDB.AddInParameter(dbCommand, "postId", DbType.Int32, postId);
            FoothillDB.AddInParameter(dbCommand, "Price", DbType.Int32, newPrice);
            FoothillDB.AddInParameter(dbCommand, "lastEditTime", DbType.DateTime, DateTime.Now);

            int rowAffected = 0;
            try
            {
                rowAffected = FoothillDB.ExecuteNonQuery(dbCommand);
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when update price for ad id={0}", postId), exception);
            }
            
            return (rowAffected > 0);
        }
        
        // construct a for-sale ad post instance
        private ForSaleAd ConstructForSaleAdInstance(IDataReader reader)
        {
            ForSaleAd instance = new ForSaleAd();

            instance.AdId = Convert.ToInt32(reader["AdId"]);
            instance.ModelId = Convert.ToInt32(reader["ModelId"]);
            instance.MakeId = Convert.ToInt32(reader["MakeId"]);
            instance.TransmissionTypeId = GetDbInt32(reader["TransmissionTypeId"], (int)TransmissionType.NA);
            instance.TransmissionTypeName = GetDbString(reader["TransmissionTypeName"], "");
            instance.YearMade = GetDbInt32(reader["YearMade"], DateTime.Now.Year);
            instance.Price = GetDbInt32(reader["Price"], 0);
            instance.MileAge = GetDbInt32(reader["MileAge"], 0);
            instance.Description = GetDbString(reader["Description"], string.Empty);
            instance.TimePosted = Convert.ToDateTime(reader["TimePosted"]);
            instance.ContactPhone = GetDbString(reader["ContactPhone"], string.Empty);
            instance.ContactEmail = GetDbString(reader["ContactEmail"], string.Empty);
            instance.ContactName = GetDbString(reader["ContactName"], string.Empty);
            instance.EditKey = GetDbString(reader["EditKey"], string.Empty);
            instance.CityId = GetDbInt32(reader["CityId"], 0);
            instance.StatusId = GetDbInt32(reader["StatusId"], 0);
            instance.LastEditTime = Convert.ToDateTime(reader["LastEditTime"]);
            instance.ModelName = GetDbString(reader["ModelName"], string.Empty);
            instance.MakeName = GetDbString(reader["MakeName"], string.Empty);
            instance.CityName = GetDbString(reader["CityName"], string.Empty);
            instance.ProfilePhotoPath = GetDbString(reader["ProfilePhotoPath"], string.Empty);
            instance.VehicleConditionName = GetDbString(reader["VehicleConditionName"], string.Empty);
            instance.ListingTypeId = GetDbInt32(reader["ListingTypeId"], 0);
            instance.ListingTypeString = GetDbString(reader["ListingTypeName"], string.Empty);
            instance.PostedByUserAccountId = GetDbInt32(reader["PostedByUserAccountId"], 0);
            instance.NumClickedOn = GetDbInt32(reader["NumClickedOn"], 0);
            instance.NumSaved = GetDbInt32(reader["NumSaved"], 0);

            return instance;
        }

        public ForSaleAd GetForSaleAdById(int id)
        {
            ForSaleAd result = new ForSaleAd();

            DbCommand dbCommand = FoothillDB.GetStoredProcCommand("GetForSaleAdById");

            FoothillDB.AddInParameter(dbCommand, "PostID", DbType.Int32, id);

            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        result = ConstructForSaleAdInstance(reader);
                    }
                }
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when get detail for for ad id={0}", id), exception);
            }

            return result;
        }

        public ForSaleAd GetForSaleAdByIdAndEditKey(int postId, string editkey)
        {
            ForSaleAd result = null;

            DbCommand dbCommand = FoothillDB.GetStoredProcCommand("GetForSaleAdByIdAndEditKey");

            FoothillDB.AddInParameter( dbCommand, "PostID", DbType.Int32, postId );
            FoothillDB.AddInParameter( dbCommand, "EditKey", DbType.String, editkey );

            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        result = ConstructForSaleAdInstance(reader);
                    }
                }
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException(string.Format("Error when get detail for for ad by id={0} and editKey={1}", postId, editkey), exception);
            }

            return result;
        }

        public IList<ForSaleAd> GetPostsByUser(int userAccountId)
        {
            string query = AdSearchRepoSqlGenerator.GetPostsByUserSql("userAccountId");

            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(query);
            FoothillDB.AddInParameter(dbCommand, "userAccountId", DbType.Int32, userAccountId);

            IList<ForSaleAd> result = new List<ForSaleAd>();
            ForSaleAd post = null;
            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        post = ConstructForSaleAdInstance(reader);
                        result.Add(post);
                    }
                }
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException("Error in GetPostsByUser for userId = " + userAccountId, exception);
            }

            return result;
        }

        public IList<ForSaleAd> GetUserSavedPosts(int userAccountId)
        {
            string sql = AdSearchRepoSqlGenerator.ConstructGetUserSavedPostsSql("userAccountId");
            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "userAccountId", DbType.Int32, userAccountId);

            IList<ForSaleAd> result = new List<ForSaleAd>();
            ForSaleAd post = null;
            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        post = ConstructForSaleAdInstance(reader);
                        result.Add(post);
                    }
                }
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException("Error in GetUserSavedPosts for userId = " + userAccountId, exception);
            }

            return result;
        }

        public void IncreaseNumberOfTimeClicked(int postId)
        {
            DbCommand dbCommand = FoothillDB.GetStoredProcCommand("IncreaseNumberOfTimeClickedOn");
            FoothillDB.AddInParameter(dbCommand, "PostId", DbType.Int32, postId);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
            }
        }

        public void RemovePostFromSavedList(int userId, int postId)
        {
            string sql = "DELETE FROM [UserSavedPost] WHERE PostId=@postId AND UserAccountId=@userId";
            
            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "postId", DbType.Int32, postId);
            FoothillDB.AddInParameter(dbCommand, "userId", DbType.Int32, userId);

            try
            {
                FoothillDB.ExecuteNonQuery(dbCommand);
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
            }
        }

        public IList<int> GetIdListForAdPostedByUser(int userAccountId)
        {
            string sql = "SELECT [AdId] FROM [AdForSale] WHERE [PostedByUserAccountId]=@userAccountId";
            DbCommand dbCommand = FoothillDB.GetSqlStringCommand(sql);
            FoothillDB.AddInParameter(dbCommand, "userAccountId", DbType.Int32, userAccountId);

            IList<int> result = new List<int>();
            try
            {
                using (IDataReader reader = FoothillDB.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        result.Add(GetDbInt32(reader["AdId"], 0));
                    }
                }
            }
            catch (Exception exception)
            {
                Log.Error(FoothillModule.DataAccess, exception.Message, exception);
                throw new DataAccessException("Error in GetIdListForAdPostedByUser for userAccountId = " + userAccountId, exception);
            }

            return result;
        }
    }
}